import mysql.connector
from mysql.connector import Error
import os
from dotenv import load_dotenv

# 加载环境变量（安全存储数据库凭据）
load_dotenv()


def get_success_reate(data):
    """查询并返回所有产品ID"""
    connection = None
    try:
        # 建立数据库连接
        # connection = mysql.connector.connect(
        #     host=os.getenv('DB_HOST', 'localhost'),
        #     user=os.getenv('DB_USER', 'root'),
        #     password=os.getenv('DB_PASSWORD', 'root'),
        #     database=os.getenv('DB_NAME', 'test'),
        #     port=os.getenv('DB_PORT', 3306)
        # )

        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'rm-uf606049v7j2kbbv35o.mysql.rds.aliyuncs.com'),
            user=os.getenv('DB_USER', 'pay'),
            password=os.getenv('DB_PASSWORD', '!@ChMwem6GdiJkq3DN'),
            database=os.getenv('DB_NAME', 'pay'),
            port=os.getenv('DB_PORT', 3109)
        )

        if connection.is_connected():
            print("成功连接到MySQL数据库!")

            # 创建游标
            cursor1 = connection.cursor()
            cursor2 = connection.cursor()


            # 执行查询
            query1 = '''SELECT 
                            success_count,
                            total_count,
                            CONCAT(
                                IF(total_count > 0, 
                                   FORMAT(success_count * 100.0 / total_count, 2), 
                                   '0.00'
                                ), 
                                '%'
                            ) AS success_rate_percent
                        FROM (
                            SELECT 
                                COUNT(CASE WHEN status = 4 THEN 1 END) AS success_count,  -- 更简洁的计数写法
                                COUNT(*) AS total_count
                            FROM pay_order
                            WHERE instime BETWEEN 1694153414 - 300 AND 1694153414
                        ) AS result;
                    '''

            query2 = '''SELECT 
                            success_count,
                            total_count,
                            CASE 
                                WHEN total_count > 0 
                                THEN CONCAT(FORMAT(100.0 * success_count / total_count, 2), '%') 
                                ELSE '0.00%'
                            END AS success_rate_percent
                            FROM (
                            SELECT 
                                COUNT(CASE WHEN status = 4 THEN 1 END) AS success_count,  -- 更简洁的计数写法
                                COUNT(*) AS total_count
                            FROM pay_order
                            WHERE 
                                instime BETWEEN 
                                    UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(1694153414))) 
                                    AND 
                                    UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(1694153414), INTERVAL 1 DAY)) - 1
                            ) AS result;
                    '''


            cursor1.execute(query1)


            # 获取所有数据
            results1 = cursor1.fetchall()


            cursor2.execute(query2)
            # 获取所有数据
            results2 = cursor2.fetchall()

            num1 = float(results2[0][2].rstrip('%'))  # "85.00%" → 85.0
            num2 = float(results1[0][2].rstrip('%'))  # "90.00%" → 90.0

            abs_diff = abs(num1 - num2)  # |85.0 - 90.0| = 5.0
            diff=num1-num2

            if num1<0.1:
                print("通道有异常，我们正在排查。")
                return "通道有异常，我们正在排查。"
            else:
                if diff>(-0.1):
                    print("成功率正常")
                    return "成功率正常。"
                else:
                    print("成功率有波动，我们正在排查")
                    return "成功率有波动，我们正在排查。"

    except Error as e:
        print(f"数据库错误: {e}")
        return []

    finally:
        # 确保关闭连接
        if connection and connection.is_connected():
            cursor1.close()
            cursor2.close()
            connection.close()
            print("数据库连接已关闭")


def display_ids(ids):
    """格式化显示产品ID"""
    if not ids:
        print("未找到任何产品ID")
        return

    print("\n产品ID列表:")
    print("-" * 30)
    for id in ids:
        print(f"ID: {id}")
    print("-" * 30)
    print(f"共找到 {len(ids)} 个产品ID")


if __name__ == "__main__":
    print("ok")
    # product_ids = get_product_ids()

    # 显示结果
    # display_ids(product_ids)